import mysql.connector
import decodemobile
import argparse
import sys
import pandas as pd

mhost = "localhost"
mport = 3306
muser = "root"
mpassword = "gaoxing"
mdatabase = "test"
pagesize = 3

def getdbconnect():
    return mysql.connector.connect(host=mhost,port=mport,user=muser,password=mpassword,database=mdatabase)

def getreadcursor(tablename,pks,columns,mdate):
    read_db = getdbconnect()
    read_cursor = read_db.cursor(dictionary=True)
    columnstr = ",".join(columns) + "," + ",".join(pks)
    read_sql = "select %s from %s where DATE_FORMAT(create_time, '%%Y%%m%%d')=%s " % (columnstr,tablename,mdate)
    print(read_sql)
    read_cursor.execute(read_sql)
    return read_db,read_cursor

def getwritecursor():
    write_db = getdbconnect()
    write_cursor = write_db.cursor()
    return write_db,write_cursor

def decodecolumn(read_db,read_cursor,write_db,write_cursor,pks,columns):
    reset = columns + pks
    while True:
        rows = read_cursor.fetchmany(pagesize)
        print(rows)
        if not rows:
            read_cursor.close()
            read_db.close()
            write_cursor.close()
            write_db.close()
            break
        else:
            old_df = pd.DataFrame(rows)

            df = old_df[reset]
            for column in columns:
                envalues = df[column].values.tolist()
                devalues = decodemobile.get_decrypt(envalues)
                df[column] = devalues
            print(df)

            condition = " "
            for pk in pks:
                condition = condition + pk + "=%s ,"
            condition = condition[:-1]

            updatestr = " "
            for column in columns:
                updatestr = updatestr + column + "=%s ,"
            updatestr = updatestr[:-1]
            print(list(df.itertuples(index=False, name=None)))
            write_cursor.executemany("insert into " + tablename + " values ()", list(df.itertuples(index=False, name=None)))
            write_cursor.executemany("update "+tablename+" set "+ updatestr +" where " + condition, list(df.itertuples(index=False, name=None)))
            write_db.commit()




def processargs():
    parser = argparse.ArgumentParser(description="decode field ")
    parser.add_argument(
        "-t", "--tablename", dest="tablename", nargs='?',  help="table name must  set")  # nargs指定参数的个数
    parser.add_argument(
        "-d", "--date", dest="mdate", nargs='?',  help="date format yyyymmdd")  # nargs指定参数的个数
    parser.add_argument(
        "-c", "--columns", dest="columns", nargs='+', default=[], help="One or more column name  must set")
    parser.add_argument(
        "-p", "--pk", dest="pks", nargs='+', default=[], help="One or more column name  must set")
    args = parser.parse_args()
    if  (len(args.columns) == 0 or args.tablename == None or args.mdate == None or len(args.pks) == 0) :
        parser.print_usage()
        sys.exit(-1)
    else:
        return args



if __name__ == '__main__':
    args = processargs()
    tablename = args.tablename
    columns = args.columns
    mdate = args.mdate
    pks = args.pks

    read_db, read_cursor=getreadcursor(tablename,pks,columns,mdate)
    write_db, write_cursor=getwritecursor()
    decodecolumn(read_db, read_cursor, write_db, write_cursor, pks, columns)


